﻿-- =============================================
-- Author:		<赵洪涛>
-- Edit date:	<2012-04-26>
-- Description:	<项目任务统计>
-- =============================================

CREATE PROCEDURE [dbo].[proc_Project_JiXiao_ProjectTask]
	(
		@CompanyId int,
		@DepartmentId int,
		@EmpState int,
		@Stext nvarchar(50),
		@StartDate nvarchar(50),
		@EndDate nvarchar (50),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	;WITH list2 As(Select Id
		,username
		,IsNull((Select companyname from company where company.id = e.companyid),'') As company
		,departmentid
		,IsNull((Select dname from department where department.id=e.departmentid),'') As department
		,e.ename
		,(Select count(0) From project_task pt Where pt.execperson=e.username And pt.isfinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As task_ncount
		,(Select count(0) From project_task pt Where pt.execperson=e.username And pt.isfinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As task_ycount
		,(Case When State<>5 Then 1 Else 2 End) As state
		From employee e	
		Where (@CompanyId=0 Or CompanyId=@CompanyId) and (@DepartmentId=0 Or DepartmentId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
		And (@Stext='' Or e.EName like '%'+@Stext+'%') And e.UserName<>''
	),
	list As(Select ROW_NUMBER() OVER (Order By task_ncount+task_ycount Desc,departmentid,username) AS Row
		,*,(Select Sum(Task_Ycount) From list2) As Task_Ycount_Sum,
		(Select Sum(Task_Ncount) From list2) As Task_Ncount_Sum
		From list2
		Where (@EmpState =0 Or state = @EmpState)
	)

Select *,(Select Count(0) From list) As Count_Sum From list Where Row Between @StartIndex and @EndIndex Order By Row

--			declare @bmbh_T  varchar(50)
--			set @bmbh_T = (select bmbh  FROM department WHERE id=@departmentId)		
--			declare @indextable table(id int identity(1,1),nid int)
--			insert into @indextable(nid) Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T;
--
--		declare @list_table_1 table(
--		id int identity(1,1)
--		,username  nvarchar(50)
--		,company nvarchar(50) /*公司名称*/
--,departmentid int
--		,department nvarchar(50) /*部门名称*/
--		,ename nvarchar(50) /*部门名称*/
--		,task_ncount int	
--		,task_ycount int
--
--		)
--
--		;WITH list As
--		(
--		select 	
--		username		
--		,(select companyname from company where company.id = e.companyid) as company
--,departmentid
--		,(select dname from department where department.id=e.departmentid) as department
--		,e.ename
--		,(select count(pt.id) from project_task pt where pt.execperson=e.username and pt.execperson <>''  and pt.isfinish=0 and (Convert(varchar(10),pt.CreateDate,23) Between @StartDate and @EndDate)) as task_ncount
--		,(select count(pt.id) from project_task pt where pt.execperson=e.username and pt.execperson <>''  and pt.isfinish=1 and (Convert(varchar(10),pt.CreateDate,23) Between @StartDate and @EndDate)) as task_ycount
--		,(Case When State<>5 Then (select  1 ) Else ( select  2  ) End) as state
--		from employee e	
--		where (@companyid=0 or e.companyid=@companyid) 
--		and (@DepartmentId = 0 or e.departmentid in(select c.nid from @indextable c)) 
--		and e.ename like  '%'+@SText+'%' 
--		and e.username <>'' --and state <> 5
--		) 
--
--		insert into @list_table_1(
--		username
--		,company 
--,departmentid
--		,department 
--		,ename 
--		,task_ncount 
--		,task_ycount 
--		) 
--		select 
--		username
--		,company 
--,departmentid
--		,department 
--		,ename 
--		,task_ncount 
--		,task_ycount 
--		From list  
--		where ( @EmpState =0 or list.state = @EmpState)
--		order by task_ycount desc ,departmentid, username;
--		-- 上面完成 list 表
--
--
--		;WITH list_return as (
--		Select ROW_NUMBER() OVER (ORDER BY Id) AS Row
--		,username
--		,company 
--		,department 
--		,ename 
--		,task_ncount 
--		,task_ycount 
--		from @list_table_1
--		)
--
--		select 
--		*
--		,(select count(0) from list_return ) AS Count_Sum
--		,( select sum( Task_Ycount) from list_return) AS Task_Ycount_Sum 
--		,( select sum( Task_Ncount) from list_return) AS Task_Ncount_Sum 
--		,ISNULL((select top 1 [Employee].state from [Employee] Where [Employee].UserName=[list_return].UserName ),0) as State 
--		from list_return  
--		Where Row between @StartIndex and @EndIndex  order by row
End
